/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Reportes;

import Model.Caja;
import java.util.Vector;
import Model.Cliente;
import Model.Congreso;
import Model.Gasto;
import Model.Ingreso;
import Model.Pedido;
import Servicio.Conexion;
import Servicio.Proxy;
import java.io.File;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Locale;
import jxl.CellReferenceHelper;
import jxl.CellView;
import jxl.HeaderFooter;
import jxl.Range;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Orientation;
import jxl.format.PageOrder;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.write.Blank;
import jxl.write.Boolean;
import jxl.write.DateFormat;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 *
 * @author usuario
 */
public class gereraCaja{
protected WritableCellFormat wrappedText;
protected WritableCellFormat arial10BoldFormat;
protected WritableWorkbook workbook;
protected String filename;
private Caja caja;
private Congreso congreso;
private   ArrayList<Gasto> gastos;
private   ArrayList<Ingreso> ingresos;
private   ArrayList<Pedido> pedidos;

    public gereraCaja( Congreso congres, ArrayList<Gasto> gasto,  ArrayList<Ingreso> ingreso, ArrayList<Pedido> pedido){
        try{
            congreso=congres;
            caja= congreso.getCaja();
            gastos=gasto;
            ingresos=ingreso;
            pedidos=pedido;
            WorkbookSettings ws = new WorkbookSettings();
            ws.setLocale(new Locale("en", "EN"));
            filename="Caja "+congreso.getIniciales()+".xls";
            workbook = Workbook.createWorkbook(new File(filename), ws);
            //formato texto
            wrappedText = new WritableCellFormat (WritableWorkbook.ARIAL_10_PT);
            wrappedText.setWrap(true);
            //formato titulos
            WritableFont arial10ptBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
            arial10BoldFormat = new WritableCellFormat(arial10ptBold);

      
        }catch(Exception e){e.printStackTrace();}
    }

    public void generaReporte(){
       try{    
       escribirInfo();
        workbook.write();
       workbook.close();
       }catch(Exception e){e.printStackTrace();}
    }
    
    protected  void escribirInfo(){
         WritableFont arial12ptBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
         WritableCellFormat  arial12BoldFormat = new WritableCellFormat(arial12ptBold);
        
        
        
        WritableSheet s1 = workbook.createSheet("Clientes", 0);
        s1.setColumnView(1,25);
        s1.setColumnView(2,25);
        s1.setColumnView(3,20);
        s1.setColumnView(4,20);
        s1.setColumnView(5,20);
        s1.setColumnView(6,20);
        s1.setColumnView(7,20);
        s1.setColumnView(8,20);
        s1.setColumnView(9,20);
        s1.setColumnView(10,20);
        try{
       
        //Carga primer fila con los titulos
        Label l1 = new Label(1,0,"Congreso", arial10BoldFormat);
        s1.addCell(l1);
        Label l2 = new Label(2,0,"Fecha Congreso", arial10BoldFormat);
        s1.addCell(l2);
        Label l3 = new Label(3,0,"Saldo Inicial", arial10BoldFormat);
        s1.addCell(l3);
         Label l4 = new Label(4,0,"Ventas en AR$", arial10BoldFormat);
        s1.addCell(l4);
         Label l5 = new Label(5,0,"Ventas en "+congreso.getSimbM2(), arial10BoldFormat);
        s1.addCell(l5);
         Label l6 = new Label(6,0,"Ventas en"+congreso.getSimbM3(), arial10BoldFormat);
        s1.addCell(l6);
        Label l7 = new Label(7,0,"Ventas Pendientes", arial10BoldFormat);
        s1.addCell(l7);
        Label l8 = new Label(8,0,"Ingresos", arial10BoldFormat);
        s1.addCell(l8);
        Label l9 = new Label(9,0,"Total Gasto", arial10BoldFormat);
        s1.addCell(l9);
        Label l10 = new Label(10,0,"Saldo final", arial10BoldFormat);
        s1.addCell(l10);
        Number n3;
        Number n4;
        Number n5;
        Number n6;
        Number n7;
        Number n8;
        Number n9;
        Number n10;
        int i=1;
            l1 = new Label(1,i,congreso.getIniciales(), wrappedText);
            s1.addCell(l1);
            l2 = new Label(2,i,congreso.getFechaInicio(), wrappedText);
            s1.addCell(l2);
            n3 = new Number(3,i,caja.getSaldoInicial(), wrappedText);
            s1.addCell(n3);
            n4 = new Number(4,i,caja.getTotalVentas(), wrappedText);
            s1.addCell(n4);
            n5 = new Number(5,i,caja.getVentasM2(), wrappedText);
            s1.addCell(n5);
            n6 = new Number(6,i,caja.getVentasM3(), wrappedText);
            s1.addCell(n6);
            n7 = new Number(7,i,caja.getVentasPendientes(), wrappedText);
            s1.addCell(n7);
            n8 = new Number(8,i,caja.getTotalIngresos(), wrappedText);
            s1.addCell(n8);
            n9 = new Number(9,i,caja.getTotalGastos(), wrappedText);
            s1.addCell(n9);
            n10 = new Number(10,i,caja.getTotal(), wrappedText);
            s1.addCell(n10);
            
         ArrayList<String> tiposg=Proxy.obtenerConjuntoExpandible(9);    
            
        i=i+3;
        l1 = new Label(1,i-1,"GASTOS", arial12BoldFormat);
        s1.addCell(l1);
        l1 = new Label(1,i,"Tipo", arial10BoldFormat);
        s1.addCell(l1);
        l2 = new Label(2,i,"Descripcion", arial10BoldFormat);
        s1.addCell(l2);
        l3 = new Label(3,i,"Fecha", arial10BoldFormat);
        s1.addCell(l3);
        l4 = new Label(4,i,"Monto", arial10BoldFormat);
        s1.addCell(l4);
         for (int j=0;j<gastos.size();j++)  {
             i=i+1;
             l1= new Label(1,i,tiposg.get(Integer.parseInt(gastos.get(j).getTipo())-1), wrappedText);
             s1.addCell(l1);
             l2= new Label(2,i,gastos.get(j).getDescripcion(), wrappedText);
             s1.addCell(l2);
             l3= new Label(3,i,gastos.get(j).getFecha(), wrappedText);
             s1.addCell(l3);
             n4 = new Number(4,i,gastos.get(j).getPrecio(), wrappedText);
            s1.addCell(n4);
         } 
        
        i=i+3;
        l1 = new Label(1,i-1,"INGRESOS", arial12BoldFormat);
        s1.addCell(l1);
        l1 = new Label(1,i,"Descripcion", arial10BoldFormat);
        s1.addCell(l1);
        l2 = new Label(2,i,"Fecha", arial10BoldFormat);
        s1.addCell(l2);
        l3 = new Label(3,i,"Monto", arial10BoldFormat);
        s1.addCell(l3);
        for (int j=0;j<ingresos.size();j++)  {
             i=i+1;
             l1= new Label(1,i,ingresos.get(j).getDescripcion(), wrappedText);
             s1.addCell(l1);
             l2= new Label(2,i,ingresos.get(j).getFecha(), wrappedText);
             s1.addCell(l2);
             n3 = new Number(3,i,ingresos.get(j).getPrecio(), wrappedText);
            s1.addCell(n3);
         } 
        
        if (congreso.getIniciales().compareTo("OFICE")==0)
            oficina(s1,i);
        else{
        i=i+3;
        l1 = new Label(1,i-1,"PEDIDOS", arial12BoldFormat);
        s1.addCell(l1);
        l1 = new Label(1,i,"Nombre", arial10BoldFormat);
        s1.addCell(l1);
        l2 = new Label(2,i,"Fecha", arial10BoldFormat);
        s1.addCell(l2);
        l3 = new Label(3,i,"Moneda", arial10BoldFormat);
        s1.addCell(l3);
        l4 = new Label(4,i,"Monto", arial10BoldFormat);
        s1.addCell(l4);
         for (int j=0;j<pedidos.size();j++)  {
             if (pedidos.get(j).getSimbM().compareTo(congreso.getSimbM1())==0){
                 i=i+1;
                 l1= new Label(1,i,pedidos.get(j).getCliente().getApellidoCli(), wrappedText);
                 s1.addCell(l1);
                 l2= new Label(2,i,pedidos.get(j).getFecha(), wrappedText);
                 s1.addCell(l2);
                 l3= new Label(3,i,pedidos.get(j).getSimbM(), wrappedText);
                 s1.addCell(l3);
                 n4 = new Number(4,i,pedidos.get(j).getTotal(), wrappedText);
                s1.addCell(n4);
             }
         } 
        
        i=i+3;
        l1 = new Label(1,i-1,"PEDIDOS", arial12BoldFormat);
        s1.addCell(l1);
        l1 = new Label(1,i,"Nombre", arial10BoldFormat);
        s1.addCell(l1);
        l2 = new Label(2,i,"Fecha", arial10BoldFormat);
        s1.addCell(l2);
        l3 = new Label(3,i,"Moneda", arial10BoldFormat);
        s1.addCell(l3);
        l4 = new Label(4,i,"Monto", arial10BoldFormat);
        s1.addCell(l4);
         for (int j=0;j<pedidos.size();j++)  {
             if (pedidos.get(j).getSimbM().compareTo(congreso.getSimbM2())==0){
                 i=i+1;
                 l1= new Label(1,i,pedidos.get(j).getCliente().getApellidoCli(), wrappedText);
                 s1.addCell(l1);
                 l2= new Label(2,i,pedidos.get(j).getFecha(), wrappedText);
                 s1.addCell(l2);
                 l3= new Label(3,i,pedidos.get(j).getSimbM(), wrappedText);
                 s1.addCell(l3);
                 n4 = new Number(4,i,pedidos.get(j).getTotal(), wrappedText);
                s1.addCell(n4);
             }
         } 
        
        i=i+3;
        l1 = new Label(1,i-1,"PEDIDOS", arial12BoldFormat);
        s1.addCell(l1);
        l1 = new Label(1,i,"Nombre", arial10BoldFormat);
        s1.addCell(l1);
        l2 = new Label(2,i,"Fecha", arial10BoldFormat);
        s1.addCell(l2);
        l3 = new Label(3,i,"Moneda", arial10BoldFormat);
        s1.addCell(l3);
        l4 = new Label(4,i,"Monto", arial10BoldFormat);
        s1.addCell(l4);
         for (int j=0;j<pedidos.size();j++)  {
             if (pedidos.get(j).getSimbM().compareTo(congreso.getSimbM3())==0){
                 i=i+1;
                 l1= new Label(1,i,pedidos.get(j).getCliente().getApellidoCli(), wrappedText);
                 s1.addCell(l1);
                 l2= new Label(2,i,pedidos.get(j).getFecha(), wrappedText);
                 s1.addCell(l2);
                 l3= new Label(3,i,pedidos.get(j).getSimbM(), wrappedText);
                 s1.addCell(l3);
                 n4 = new Number(4,i,pedidos.get(j).getTotal(), wrappedText);
                s1.addCell(n4);
             }
         } 
        }//finalisa el else de no oficina
        }catch(Exception e){e.printStackTrace();}
        
    }
    
private void oficina(WritableSheet s1,int i){
    ArrayList<String> monedas = new ArrayList<String>();
    for (int k=0;k<pedidos.size();k++){
        int flag=0;
        for (int j=0;j<monedas.size();j++){
            if (monedas.get(j).compareTo(pedidos.get(k).getSimbM())==0)
                flag=1;
        }
        if (flag==0)
            monedas.add(pedidos.get(k).getSimbM());              
    }
    
    try{
        for (int k=0; k<monedas.size();k++){
             i=i+3;
            Label l1 = new Label(1,i-1,"PEDIDOS", arial10BoldFormat);
            s1.addCell(l1);
            l1 = new Label(1,i,"Nombre", arial10BoldFormat);
            s1.addCell(l1);
            Label l2 = new Label(2,i,"Fecha", arial10BoldFormat);
            s1.addCell(l2);
            Label l3 = new Label(3,i,"Moneda", arial10BoldFormat);
            s1.addCell(l3);
            Label l4 = new Label(4,i,"Monto", arial10BoldFormat);
            s1.addCell(l4);
             for (int j=0;j<pedidos.size();j++)  {
                 if (pedidos.get(j).getSimbM().compareTo(monedas.get(k))==0){
                     i=i+1;
                     l1= new Label(1,i,pedidos.get(j).getCliente().getApellidoCli(), wrappedText);
                     s1.addCell(l1);
                     l2= new Label(2,i,pedidos.get(j).getFecha(), wrappedText);
                     s1.addCell(l2);
                     l3= new Label(3,i,pedidos.get(j).getSimbM(), wrappedText);
                     s1.addCell(l3);
                    Number  n4 = new Number(4,i,pedidos.get(j).getTotal(), wrappedText);
                    s1.addCell(n4);
                 }
         } 
        }
        
    }catch(Exception e){e.printStackTrace();}
}


}


